import requests
import datetime
import xlsxwriter
import time
import pymysql
from DBUtils.PooledDB import PooledDB
import threading
import math
import pandas as pd
from time import *


ID = "ww7b4086d88a144eec"
SECRET = "QqZ9Au1EwEDc1PyvcZaAgJBhaPDeK1z5zinENKHLL1Q"

def pool():
    pool = PooledDB(pymysql, 15, host='localhost', user='root', passwd='zcxvbmn', db='weixindb', port=3306)
    return pool

# 获取access_token
url_get_access_token = "https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=" + ID + "&corpsecret=" + SECRET
def get_access_token(ID,SECRET):
    access_token_json = requests.get(url_get_access_token).json()
    print(access_token_json)
    while access_token_json['errcode'] != 0:
        get_access_token(ID, SECRET)
        time.sleep(2)
    access_token = access_token_json['access_token']
    return access_token

# access_token缓存
access_token = get_access_token(ID,SECRET)


# 获取企业全部的发表列表(个人的从这里拿)
url_get_1v1_list = "https://qyapi.weixin.qq.com/cgi-bin/externalcontact/get_groupmsg_list_v2?access_token=" + access_token
def clean_and_get_1v1_list(start_time, end_time, type):
    delete_sql = "delete from 1v1_list"
    db = pool().connection()
    cursor = db.cursor()
    cursor.execute(delete_sql)
    i = 1
    next_cursor = ''
    while i == 1 or next_cursor != '':
        1v1_publish_inform = {
            "start_time": start_time,
            "end_time": end_time,
            "filter_type": type,#type字段 0为企业发表，1为个人发表，2为所有
            "cursor": next_cursor,
            "limit": 20
        }
        moment_publish = requests.post(url_get_moment_list, json=moment_publish_inform)
        moment_list = moment_publish.json()['moment_list'] #moment_list是个列表
        tuple_moment_list = []
        insert_sql = ""
        for moment in moment_list:
            moment_id = (moment['moment_id'],)
            try:
                creator = (moment['creator'],)
            except:
                creator = ("/",)
            create_type = (moment['create_type'],)
            visible_type = (moment['visible_type'],)
            try:
                text = (moment['text']['content'][:50],)
            except:
                text = ("/",)
            try:
                media_id = moment['video']['media_id']
                video = ("是",)
            except:
                video = ("否",)
            tuple_moment = moment_id + creator + create_type + visible_type + text + video
            insert_sql = "insert into moment_list(moment_id,creator,create_type,visible_type,text,video) values(%s,%s,%s,%s,%s,%s)"
            tuple_moment_list.append(tuple_moment)
        cursor.executemany(insert_sql, tuple_moment_list)
        db.commit()
        next_cursor = moment_publish.json()['next_cursor']
        i = i + 1
    db.close()
    return